﻿Imports PIA = Microsoft.Office.Interop.Excel

Public Class Estados
    Dim con As New Conexion_SQL(cst_SAE)

    Dim m_Excel As New PIA.Application
    Dim m_Hoja As New PIA.Worksheet

    Dim dtRes As DataTable
    Dim dtCuentas As DataTable

    Dim carga As Boolean = False

    Private Sub btnBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscar.Click
        dgTabla.Visible = False
        btnImportar.Visible = False

        Dim xobj As New System.Windows.Forms.OpenFileDialog
        Dim nHoja As String = ""

        Dim openFD As New OpenFileDialog()
        openFD.Title = "Seleccionar archivos"
        openFD.Filter = "Todos los archivos *.xls|*.xls"
        openFD.Multiselect = False
        openFD.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
        openFD.ShowDialog()
        txtRuta.Text = openFD.FileName

        If txtRuta.Text.Length > 0 Then
            carga_cuentas()
            Label2.Visible = True
            cmbCuenta.Visible = True
            Label3.Visible = True
            dtpFecha.Value = DateAdd(DateInterval.Day, -1, Now)
            dtpFecha.Visible = True
            Dim cta As String = txtRuta.Text.Substring(txtRuta.Text.LastIndexOf("\")).Replace("\", "").Replace(".xls", "")
            Dim i As Integer = -1
            For Each itm As String In cmbCuenta.Items
                i += 1
                If itm.Split(" - ").GetValue(4).ToString.Contains(cta) Then
                    Exit For
                End If
            Next
            If i > -1 Then
                cmbCuenta.SelectedIndex = i
            End If
        End If
    End Sub

    Private Sub carga_tabla()
        m_Excel = New PIA.Application
        m_Excel.Workbooks.Open(txtRuta.Text)
        m_Excel.Visible = False

        m_Hoja = m_Excel.Worksheets(1)

        dtRes = New DataTable

        Me.Cursor = Cursors.WaitCursor

        Application.DoEvents()

        Dim i As Integer = 1
        Do
            dtRes.Columns.Add(m_Hoja.Cells(1, i).value)
            i += 1
        Loop Until (m_Hoja.Cells(1, i).value Is Nothing)
        dtRes.Columns.Add("Referencia")

        i = 2
        Do
            Dim fila As DataRow = dtRes.NewRow
            For j As Integer = 1 To dtRes.Columns.Count
                fila(j - 1) = m_Hoja.Cells(i, j).value
            Next
            dtRes.Rows.Add(fila)
            i += 1
        Loop Until (m_Hoja.Cells(i, 1).value Is Nothing OrElse m_Hoja.Cells(i, 1).GetType.ToString = "Double" OrElse m_Hoja.Cells(i, 1).value.ToString.Contains("Pagina") OrElse m_Hoja.Cells(i, 1).value.ToString.Contains("Página") OrElse m_Hoja.Cells(i, 1).value.ToString = "")

        m_Excel.Workbooks.Close()
        dtRes.Columns.Add("Traspaso", GetType(Boolean))
        dtRes.Columns.Add("Cheque", GetType(Boolean))

        For Each col As DataColumn In dtRes.Columns
            col.ColumnName = col.ColumnName.Replace(" ", "_")
        Next

        For Each fila As DataRow In dtRes.Select(dtRes.Columns(3).ColumnName & "='-DESCRIPCION NO DISP-'")
            dtRes.Rows.Remove(fila)
        Next

        Calcula_Movimientos()

        dgTabla.DataSource = dtRes
        dgTabla.Visible = True
        btnImportar.Visible = True

        Me.Cursor = Cursors.Default
    End Sub

    Private Sub Calcula_Movimientos()

        Dim iD As Integer = 0
        Dim iR As Integer = 0
        Dim iT As Integer = 0
        Dim iC As Integer = 0

        For Each fila As DataRow In dtRes.Rows
            If (fila("Referencia").ToString.ToUpper.Contains("TRASP") And fila("Traspaso") Is DBNull.Value) OrElse (Not fila("Traspaso") Is DBNull.Value AndAlso fila("Traspaso") = True) Then
                fila("Traspaso") = True
            Else
                fila("Traspaso") = False
            End If

            If (fila("Ref_Alfanumérica").ToString.ToUpper.Contains("CHEQ") And fila("Cheque") Is DBNull.Value) OrElse (Not fila("Cheque") Is DBNull.Value AndAlso fila("Cheque") = True) Then
                fila("Cheque") = True
            Else
                fila("Cheque") = False
            End If

            If Not (fila("Traspaso") Is DBNull.Value) AndAlso fila("Traspaso") = True AndAlso fila("Cheque") = False Then
                iT += 1
            End If

            If Not (fila("Cheque") Is DBNull.Value) AndAlso fila("Cheque") = True AndAlso fila("Traspaso") = False Then
                iC += 1
            End If

            If fila("Retiros") Is DBNull.Value AndAlso fila("Depósitos") > 0 AndAlso fila("Traspaso") = False AndAlso fila("Cheque") = False Then
                iD += 1
            End If


            If fila("Depósitos") Is DBNull.Value AndAlso fila("Retiros") > 0 AndAlso fila("Traspaso") = False AndAlso fila("Cheque") = False Then
                iR += 1
            End If

        Next

        lbDepositos.Text = ""
        lbRetiros.Text = ""
        lbTraspasos.Text = ""

        If iD > 0 Then lbDepositos.Text = iD.ToString & " Depositos"
        If iR > 0 Then lbRetiros.Text = iR.ToString & " Retiros"
        If iT > 0 Then lbTraspasos.Text = iT.ToString & " Traspasos"
        If iC > 0 Then lbCheque.Text = iC.ToString & " Cheques"

        lbDepositos.Visible = True
        lbRetiros.Visible = True
        lbTraspasos.Visible = True
        lbCheque.Visible = True
    End Sub

    Private Sub carga_cuentas()
        carga = False

        Dim cad As String = "select CVE_CTA, 1 AS EMP, 'PAPALOTE' AS EMPRESA ,NUM_CTA, BANCO from BANPAP.dbo.CTAS01 "
        cad += "UNION ALL "
        cad += "select CVE_CTA, 2 AS EMP, 'SERVIMUSEOS' AS EMPRESA ,NUM_CTA, BANCO from BANSER.dbo.CTAS02 "

        dtCuentas = New DataTable
        con.OpenQueryIntoDataTable(dtCuentas, cad)

        cmbCuenta.Items.Clear()
        For Each fila As DataRow In dtCuentas.Rows
            Dim cve As String = fila("EMPRESA")
            cve += " - "
            cve += fila("BANCO")
            cve += " - "
            cve += fila("NUM_CTA")
            cmbCuenta.Items.Add(cve)
        Next

        cmbCuenta.SelectedIndex = -1

        carga = True
    End Sub

    Private Sub cmbCuenta_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCuenta.SelectedIndexChanged
        If carga Then carga_tabla()
    End Sub

    Private Sub dgTabla_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgTabla.CellContentClick
        If e.ColumnIndex = dgTabla.Columns.Count - 1 Then
            If dtRes.Rows(e.RowIndex)(e.ColumnIndex) = False Then
                dtRes.Rows(e.RowIndex)(e.ColumnIndex) = True
                dtRes.Rows(e.RowIndex)(e.ColumnIndex - 1) = False
            Else
                dtRes.Rows(e.RowIndex)(e.ColumnIndex) = False
            End If
            Calcula_Movimientos()
        End If

        If e.ColumnIndex = dgTabla.Columns.Count - 2 Then
            If dtRes.Rows(e.RowIndex)(e.ColumnIndex) = False Then
                dtRes.Rows(e.RowIndex)(e.ColumnIndex) = True
                dtRes.Rows(e.RowIndex)(e.ColumnIndex + 1) = False
            Else
                dtRes.Rows(e.RowIndex)(e.ColumnIndex) = False
            End If
            Calcula_Movimientos()
        End If
    End Sub

    Private Sub btnImportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportar.Click

        Dim cad As String
        con.ConnectDataBase(cst_Papalote)

        Dim movimientos As Integer = con.QueryGetFieldTmpCnn("SELECT COUNT(*) FROM ECUENTAS WHERE CONVERT(DATETIME,FECHA,111)=CONVERT(DATETIME,'" & Format(dtpFecha.Value, "yyyy-MM-dd") & "',111) AND CUENTA LIKE '" & dtCuentas.Rows(cmbCuenta.SelectedIndex)("NUM_CTA") & "'")

        If movimientos > 0 Then
            If MessageBox.Show("Ya hay " & movimientos & " movimientos de la cuenta " & dtCuentas.Rows(cmbCuenta.SelectedIndex)("NUM_CTA") & " del dia " & Format(dtpFecha.Value, "yyyy-MM-dd") & vbCrLf & vbCrLf & "Deseas continuar?", "Atención", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If
        Else
            Dim mensaje As String

            mensaje = "Fecha:    " & Format(dtpFecha.Value, "dd/MM/yyyy")
            mensaje += vbCrLf & "Cuenta:   " & dtCuentas.Rows(cmbCuenta.SelectedIndex)("NUM_CTA") & vbCrLf

            If lbDepositos.Text.Trim.Length > 0 Then mensaje += vbCrLf & lbDepositos.Text
            If lbRetiros.Text.Trim.Length > 0 Then mensaje += vbCrLf & lbRetiros.Text
            If lbTraspasos.Text.Trim.Length > 0 Then mensaje += vbCrLf & lbTraspasos.Text
            If lbCheque.Text.Trim.Length > 0 Then mensaje += vbCrLf & lbCheque.Text

            mensaje += vbCrLf & vbCrLf & "Deseas continuar?"


            If MessageBox.Show(mensaje, "Confirma la siguiente información", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If
        End If

        For Each fila As DataRow In dtRes.Rows

            Dim dep As Double = 0
            Dim ret As Double = 0
            Dim tra As Integer = 0
            Dim che As Integer = 0

            If fila("Retiros") Is DBNull.Value Then
                ret = 0
            Else
                ret = CDbl(fila("Retiros"))
            End If

            If fila("Depósitos") Is DBNull.Value Then
                dep = 0
            Else
                dep = CDbl(fila("Depósitos"))
            End If

            If fila("Traspaso") = True Then
                tra = 1
            Else
                tra = 0
            End If

            If fila("Cheque") = True Then
                che = 1
            Else
                che = 0
            End If

            cad = "INSERT INTO ECUENTAS (FECHA,CUENTA,SUCURSAL,AUTORIZACION,RTEXTO,RNUMERO,RETIROS,DEPOSITOS,REFERENCIA,TRASPASO,CHEQUE)"
            cad += vbCrLf & "VALUES("
            cad += vbCrLf & "convert(datetime,'" & Format(dtpFecha.Value, "yyyy-MM-dd") & "',111),"
            cad += vbCrLf & "'" & dtCuentas.Rows(cmbCuenta.SelectedIndex)("NUM_CTA") & "',"
            cad += vbCrLf & "'" & fila("Sucursal") & "',"
            cad += vbCrLf & "'" & fila("Autorización") & "',"
            cad += vbCrLf & "'" & fila("Ref_Alfanumérica") & "',"
            cad += vbCrLf & "'" & fila("Ref_Numérica") & "',"
            cad += vbCrLf & ret.ToString & ","
            cad += vbCrLf & dep.ToString & ","
            cad += vbCrLf & "'" & fila("Referencia") & "',"
            cad += vbCrLf & tra & "," & che & ")"
            'cad += vbCrLf &

            'FECHA, CUENTA, SUCURSAL, AUTORIZACION, RTEXTO, RNUMERO, RETIROS, DEPOSITOS, REFERENCIA, TRASPASO)"
            con.ExecuteSQL(cad)
        Next

        MessageBox.Show("Se han guardado los movimientos con éxito", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Label2.Visible = False
        Label3.Visible = False
        dtpFecha.Value = DateAdd(DateInterval.Day, -1, Now)
        dtpFecha.Visible = False
        cmbCuenta.Visible = False
        txtRuta.Text = ""
        dgTabla.Visible = False
        dgTabla.DataSource = Nothing
        lbDepositos.Visible = False
        lbRetiros.Visible = False
        lbTraspasos.Visible = False
        lbCheque.Visible = False
        txtRuta.Focus()

    End Sub
End Class